package dcw.excel;


import dcw.excel.model.Rule;
import dcw.excel.utils.SerializationUtils;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.text.DecimalFormat;
import java.util.*;

public class ExcelReader {

    private static final int ruleRow = 4;
    private static final String XLS = "xls";
    private static final String XLSX = "xlsx";

    /**
     * 根据文件后缀名类型获取对应的工作簿对象
     *
     * @param inputStream 读取文件的输入流
     * @param fileType    文件后缀名类型（xls或xlsx）
     * @return 包含文件数据的工作簿对象
     * @throws IOException
     */
    public static Workbook getWorkbook(InputStream inputStream, String fileType) throws IOException {
        Workbook workbook = null;
        if (fileType.equalsIgnoreCase(XLS)) {
            workbook = new HSSFWorkbook(inputStream);
        } else if (fileType.equalsIgnoreCase(XLSX)) {
            workbook = new XSSFWorkbook(inputStream);
        }
        return workbook;
    }

    /**
     * 读取Excel文件内容
     *
     * @param fileName 要读取的Excel文件所在路径
     * @return 读取结果列表，读取失败时返回null
     */
    public static ArrayList<Map<String,Object>> readExcel(String fileName) {
        Workbook workbook = null;
        FileInputStream inputStream = null;
        try {
            // 获取Excel后缀名
            String fileType = fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length());
            // 获取Excel文件
            File excelFile = new File(fileName);
            if (!excelFile.exists()) {
                System.err.println("指定的Excel文件不存在！");
                return new ArrayList<>();
            }
            // 获取Excel工作簿
            inputStream = new FileInputStream(excelFile);
            workbook = getWorkbook(inputStream, fileType);

            // 读取excel中的数据
            ArrayList<Map<String,Object>> resultDataList = parseExcel(workbook);
            return resultDataList;
        } catch (Exception e) {
            System.err.println("解析Excel失败，文件名：" + fileName + " 错误信息：" + e.getMessage());
            return new ArrayList<>();
        } finally {
            try {
                if (null != workbook) {
                    workbook.close();
                }
                if (null != inputStream) {
                    inputStream.close();
                }
            } catch (Exception e) {
                System.err.println("关闭数据流出错！错误信息：" + e.getMessage());
                return new ArrayList<>();
            }
        }
    }

    /**
     * 读取Excel文件内容
     *
     * @param fileName 要读取的Excel文件所在路径
     * @return 读取结果列表，读取失败时返回null
     */
    public static ArrayList<Map<String,Object>> readExcel(FileInputStream inputStream,String fileName) {
        Workbook workbook = null;
        try {
            // 获取Excel后缀名
            String fileType = fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length());
            // 获取Excel工作簿
            workbook = getWorkbook(inputStream, fileType);

            // 读取excel中的数据
            ArrayList<Map<String,Object>> resultDataList = parseExcel(workbook);
            return resultDataList;
        } catch (Exception e) {
            System.err.println("解析Excel失败，文件名：" + fileName + " 错误信息：" + e.getMessage());
            return new ArrayList<>();
        } finally {
            try {
                if (null != workbook) {
                    workbook.close();
                }
                if (null != inputStream) {
                    inputStream.close();
                }
            } catch (Exception e) {
                System.err.println("关闭数据流出错！错误信息：" + e.getMessage());
                return new ArrayList<>();
            }
        }
    }

    /**
     * 解析Excel数据
     *
     * @param workbook Excel工作簿对象
     * @return 解析结果
     */
    private static ArrayList<Map<String,Object>> parseExcel(Workbook workbook) {
        FormulaEvaluator e = workbook.getCreationHelper().createFormulaEvaluator();
//        if(workbook instanceof HSSFWorkbook) {
//            e = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
//        } else {
//            e = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
//        }
//        workbook.getCreationHelper().createFormulaEvaluator()
        ArrayList<Map<String,Object>> resultDataList = new ArrayList<>();
        // 解析sheet
        Sheet sheet = workbook.getSheetAt(0);
        // 校验sheet是否合法
        if (sheet == null) {
            System.err.println("解析Excel失败，sheet 读取不到任何数据 ！");
            return resultDataList;
        }
        // 获取第一行数据
        int firstRowNum = sheet.getFirstRowNum();
        Row firstRow = sheet.getRow(firstRowNum);
        if (null == firstRow) {
            System.err.println("解析Excel失败，在第一行没有读取到任何数据！");
        }
        Rule[] rules = initRule(sheet);
        if(Arrays.stream(rules).filter(r -> !Objects.isNull(r)).count() != 0) {
            // 解析每一行的数据，构造数据对象
            int rowEnd = sheet.getPhysicalNumberOfRows();
            for (int rowNum = ruleRow; rowNum < rowEnd; rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (null == row || isRowEmpty(row) || (row.getFirstCellNum() == 0 && row.getCell(0).getStringCellValue().equals("#"))) {
                    continue;
                }
                Map<String, Object> resultData = convertRowToData(e, row, rules);
                if (null == resultData) {
                    System.err.println("第 " + row.getRowNum() + "行数据不合法，已忽略！");
                    continue;
                }
                resultDataList.add(resultData);
            }
        }
        return resultDataList;
    }

    public static boolean isRowEmpty(Row row) {
        for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
            Cell cell = row.getCell(c);

            if (cell != null && cell.getCellType() != CellType.BLANK && (cell.getCellType() == CellType.STRING && !cell.getStringCellValue().isBlank())){
                return false;
            }
        }
        return true;
    }

    private static Rule[] initRule(Sheet sheet) {
        int firstRowNum = sheet.getFirstRowNum();
        Row row = sheet.getRow(firstRowNum);
        Row recordRow = sheet.getRow(firstRowNum + 1);
        Row markRow = sheet.getRow(firstRowNum + 2);
        Row nameRow = sheet.getRow(firstRowNum + 3);
        Rule[] rules = new Rule[row.getPhysicalNumberOfCells()];
        int cellNum;
        for (cellNum = 1; cellNum < row.getPhysicalNumberOfCells(); cellNum++) {
            if(isServer(markRow,cellNum)) {
                String cValue = row.getCell(cellNum).getStringCellValue();
                Rule rule = new Rule(cellNum, cValue);
                rules[cellNum] = rule;
            }
        }
        Arrays.stream(rules).filter(r -> !Objects.isNull(r)).forEach(rule -> {
            rule.setMark(recordRow.getCell(rule.getIndex()).getStringCellValue());
            rule.setFiledName(nameRow.getCell(rule.getIndex()).getStringCellValue());
        });
        return rules;
    }

    private static boolean isServer(Row row,int index) {
        Cell cell = row.getCell(index);
        String cValue = "";
        if(cell != null) {
            cValue = cell.getStringCellValue();
        }
        return cValue.equalsIgnoreCase("both") || cValue.equalsIgnoreCase("server");
    }

    private static String convertCell(FormulaEvaluator e,Cell cell) {
        String value = "";
        switch (cell.getCellType()) {
            case NUMERIC:
                DecimalFormat formatter = new DecimalFormat("########");
                value = String.valueOf(formatter.format(cell.getNumericCellValue()));
                break;
            case FORMULA:   // 公式
                e.setIgnoreMissingWorkbooks(true);
                Cell c = e.evaluateInCell(cell);
                value = convertCell(e,c);
                break;
            default: value = cell.getStringCellValue();
        }
        return value;
    }

    /**
     * 将单元格内容转换为字符串
     *
     * @param cell
     * @return
     */
    private static Object convertCellValueToString(Cell cell, String filedType, FormulaEvaluator e) {
        if (cell == null) {
            return null;
        }
        String value = convertCell(e,cell);
        Object returnValue = null;
        switch (filedType) {
            case "int":   //数字
                returnValue = Integer.valueOf(value);
                break;
            case "string":    //字符串
                returnValue = value;
                break;
            case "bool":   //布尔
                returnValue = value.equals("0");
                break;
            case "float":
                returnValue = Float.valueOf(value);
                break;
            case "array[int]":  //int数组
                returnValue = Arrays.stream(value.split("\\|")).mapToInt(Integer::parseInt).toArray();
                break;
            case "array[string]"://字符串数组
                returnValue = value.split("\\|");
                break;
            case "array[float]":
                returnValue = Arrays.stream(value.split("\\|")).map(Float::parseFloat).toArray();
                break;
            default:
                break;
        }
        return returnValue;
    }

    /**
     * 提取每一行中需要的数据，构造成为一个结果数据对象
     * <p>
     * 当该行中有单元格的数据为空或不合法时，忽略该行的数据
     *
     * @param row 行数据
     * @return 解析后的行数据对象，行数据错误时返回null
     */
    private static Map<String,Object>  convertRowToData(FormulaEvaluator e,Row row,Rule[] rules) {
        Map<String,Object> resultData = new HashMap<>();
        Arrays.stream(rules).filter(r -> !Objects.isNull(r)).forEach(rule -> {
            Object value = convertCellValueToString(row.getCell(rule.getIndex()),rule.getFiledType(),e);
            resultData.put(rule.getFiledName(),value);
        });
        return resultData;
    }

    private static HashMap<String,List<Map<String,Object>>> readFile(String path) {
        HashMap<String,List<Map<String,Object>>> map = new HashMap();
        File file = new File(path);
        File[] files = file.listFiles(new FilenameFilter() {
            private String tag=".xlsx";
            @Override
            public boolean accept(File dir, String name) {
                return name.endsWith(tag);
            }
        });
        for (File f : files) {
            try {
                if (!f.exists()) {
                    System.err.println("指定的Excel文件不存在！ name = " + f.getName());
                } else {
                    List<Map<String,Object>> data = readExcel(new FileInputStream(f), f.getName());
                    if(!data.isEmpty()) map.put(f.getName(), data);
                }
            } catch (FileNotFoundException e) {
                System.err.println("文件未找到  file name :" +f.getName());
                continue;
            }
        }
        return map;
    }

    public static void main(String[] args) throws IOException {
//        long stime = System.currentTimeMillis();
//        ArrayList<Map<String,Object>> datas = readExcel("D:\\workspace\\ClientProjectA\\Data\\Table\\item.xlsx");
//        System.err.println("======================  data size:"+ datas.size());
//
//        byte[] bytes = SerializationUtils.serialize(datas);
//        System.err.println("===================== bytes size:" + bytes.length);
//
//        ArrayList<Map<String,Object>> deserdatas =  SerializationUtils.deserialize(bytes);
//        deserdatas.forEach(d -> {
//            d.forEach((k,v)-> System.err.println(" k = " + k + " and v = " + v));
//        });
//
//        FileOutputStream fileWriter = new FileOutputStream("d:\\data.dat");
//        fileWriter.write(bytes);
//        fileWriter.close();
//
//        FileInputStream fileInputStream = new FileInputStream("d:\\data.dat");
//        byte[] bytesByDat = fileInputStream.readAllBytes();
//        System.err.println("+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++           ");
//
//        List<Map<String,Object>> deserdatas1 =  SerializationUtils.deserialize(bytesByDat);
//        deserdatas1.forEach(d -> {
//            d.forEach((k,v)-> System.err.println(" k = " + k + " and v = " + v));
//        });
//        fileInputStream.close();
//        long etime = System.currentTimeMillis();
//        System.err.println(" time = "+ (etime -stime));



//        /**               所有的table   start                   **/
//        HashMap<String,List<Map<String,Object>>> map = readFile("D:\\workspace\\ServerProject-product\\table");
//        System.err.println("========");
//        byte[] bytes = SerializationUtils.serialize(map);
//        System.err.println("===================== bytes size:" + bytes.length);
//
//        Map<String,List<Map<String,Object>>> deserdatas =  SerializationUtils.deserialize(bytes);
//        deserdatas.forEach((k1,v1) -> {
//            v1.forEach(v2 -> v2.forEach((k,v)-> System.err.println("k1 = "+k1+" k = " + k + " and v = " + v)));
//        });
//
//        FileOutputStream fileWriter = new FileOutputStream("d:\\data.dat");
//        fileWriter.write(bytes);
//        fileWriter.close();
//
//        FileInputStream fileInputStream = new FileInputStream("d:\\data.dat");
//        byte[] bytesByDat = fileInputStream.readAllBytes();
//        System.err.println("+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++          bytesByDat = " + bytesByDat.length);
//        fileInputStream.close();
//        /**                       end                            **/


        Map<String,List<Map<String,Object>>> map = readFile("D:\\workspace\\ServerProject-product\\table");
        System.err.println("====");
    }
}
